const express = require('express');
const router = express.Router();
const db = require('../utils/db');

//渲染
router.post('/getStudent', (req, res) => {
  const { page, count, search } = req.body;
  const sql = `SELECT s_id,s_name,s_tel,s_idCard,g_grade,c_class,s_status FROM p_student LEFT JOIN p_class ON p_student.s_class=p_class.c_id LEFT JOIN p_grade ON p_class.c_grade=p_grade.g_id WHERE s_name LIKE '%${search}%' LIMIT ${
    (page - 1) * count
  },${count};`;
  const sql2 = `SELECT COUNT(*) AS total FROM p_student WHERE s_name LIKE '%${search}%'`;
  db.query(sql, (err, data) => {
    if (err) throw err;
    db.query(sql2, (err2, data2) => {
      if (err2) throw err2;
      res.send({
        error: 0,
        msg: '数据获取成功',
        data,
        total: data2[0].total,
      });
    });
  });
});

//年级id
router.post('/getGradeId', (req, res) => {
  const sql = `SELECT g_id,g_grade FROM p_grade`;
  db.query(sql, (err, data) => {
    if (err) throw err;
    res.send({ error: 0, msg: '数据获取成功', data });
  });
});
//班级id
router.post('/selClass', (req, res) => {
  const { gradeId } = req.body;
  const sql = `SELECT c_id,c_class FROM p_class WHERE c_grade=${gradeId}`;
  db.query(sql, (err, data) => {
    if (err) throw err;
    res.send({ error: 0, msg: '数据获取成功', data });
  });
});

//新增
router.post('/addStudent', (req, res) => {
  const { name, tel, idCard, classId, status } = req.body;
  const sql = `INSERT INTO p_student VALUE(NULL,'${name}','${tel}','${idCard}',${classId},${status})`;
  db.query(sql, (err, data) => {
    if (err) throw err;
    res.send({ error: 0, msg: '添加成功', data });
  });
});

//删除
router.post('/delStudent', (req, res) => {
  const { id } = req.body;
  const sql = `DELETE FROM p_student WHERE s_id=${id};`;
  db.query(sql, (err, data) => {
    if (err) throw err;
    res.send({ error: 0, msg: '删除成功', data });
  });
});

//编辑
router.post('/editStudent', (req, res) => {
  const { id, name, tel, idCard, status } = req.body;
  const sql = `UPDATE p_student SET s_name='${name}',s_tel='${tel}',s_idCard='${idCard}',s_status=${status} WHERE s_id=${id};`;
  db.query(sql, (err, data) => {
    if (err) throw err;
    res.send({ error: 0, msg: '编辑成功', data });
  });
});
module.exports = router;